package net.pws.oos.biz.service.impl;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.pws.common.persistence.EntityManager;
import net.pws.common.util.StringUtils;
import net.pws.oos.biz.BizException;
import net.pws.oos.biz.model.IndicatorValue;
import net.pws.oos.biz.model.Project;
import net.pws.oos.biz.model.StationRecord;
import net.pws.oos.biz.model.Station;
import net.pws.oos.biz.model.User;
import net.pws.oos.biz.query.IndicatorFinder;
import net.pws.oos.biz.query.ProjectFinder;
import net.pws.oos.biz.query.StationFinder;
import net.pws.oos.biz.service.StationService;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional
public class DefaultStationService implements StationService {

	@Autowired
	private EntityManager entityManager;

	@Autowired
	private StationFinder stationFinder;

	@Autowired
	private ProjectFinder projectFinder;

	@Autowired
	private IndicatorFinder indicatorFinder;

	@Transactional
	public Station saveStation(Station station) {
		entityManager.persist(station);
		return station;
	}

	@Transactional
	public List<Station> importStation(InputStream in, String configFilePath, StringBuffer errorMsg, String userId) throws Exception {
		if (StringUtils.isBlank(configFilePath)) {
			throw new BizException("导入异常：导入配置文件不存在");
		}
		File configFile = new File(configFilePath);
		if (null == configFile || !configFile.exists()) {
			throw new BizException("导入异常：导入配置文件不存在");
		}
		// {  列名 : 字段 }
		Map<String, String[]> configMap = readConfigFile(configFile);
		
		User creator = entityManager.find(User.class, userId);
		
		List<Station> stations = readStationInfofromXls(in, configMap, errorMsg, creator);
		if (errorMsg.length() == 0) {
			for (Station station : stations) {
				// mapper.updateByConditions(station);
				//任务名称为空是取基站名称作为任务名称
				if(StringUtils.isBlank(station.getName())){
					station.setName(station.getBaseStationName());
				}
				saveStation(station);
				if(station.getStationRecord() != null){
					entityManager.persist(station.getStationRecord());
				}
			}
			return stations;
		}
		return null;
	}

	private Map<String, String[]> readConfigFile(File configFile) {
		Map<String, String[]> configMap = new HashMap<String, String[]>();

		FileInputStream in = null;
		InputStreamReader reader = null;
		BufferedReader br = null;
		try {
			in = new FileInputStream(configFile);
			reader = new InputStreamReader(in,"UTF-8");
			br = new BufferedReader(reader);
			String content = br.readLine();
			while (content != null) {
				String[] ss = content.split("_");
				if (ss.length == 2 && !StringUtils.isBlank(ss[0])) {
					for(String s : ss[1].split(",")){
						configMap.put(s, ss[0].split(","));
					}
				}
				content = br.readLine();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new BizException("导入异常：导入配置文件读取失败");
		}finally{
			if(null!=br){try {br.close();}catch(Exception ignore){}}
			if(null!=reader){try {reader.close();}catch(Exception ignore){}}
			if(null!=in){try {in.close();}catch(Exception ignore){}}
		}

		return configMap;
	}

	@SuppressWarnings("unchecked")
	private List<Station> readStationInfofromXls(InputStream is,Map<String, String[]> configMap, StringBuffer errorMsg, User creator) throws Exception {
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		List<Station> stations = new ArrayList<Station>();
		Station station;
		// 循环工作表Sheet
		// for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets();
		// numSheet++) {
		// HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
		HSSFSheet hssfSheet = hssfWorkbook.getSheet("查勘记录表");
		if (hssfSheet == null) {
			// continue;
			throw new BizException("导入异常：查勘记录表不存在");
		}
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
		// 循环行Row
		for (int rowNum = 3; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {

			HSSFRow hssfRow = hssfSheet.getRow(rowNum);

			HSSFCell brandIdHSSFCell = hssfRow.getCell(0);
			// 第一个单元格为空时，停止读取记录
			String val = readValue(brandIdHSSFCell);
			if (StringUtils.isBlank(val) || "合计".equals(val)) {
				station = null;
				break;
			} else {
				station = new Station();
				StationRecord recod = new StationRecord(station);
				station.setStationRecord(recod);
				station.setCreator(creator);
				station.setCreateDateTime(new Date());
			}
			short cellNum = 108;// hssfRow.getLastCellNum()
			for (int i = 1; i < cellNum; i++) {
				brandIdHSSFCell = hssfRow.getCell(i);
				val = readValue(brandIdHSSFCell);
				
				
				String columnName = getColumnName(i);
				if(configMap.containsKey(columnName)){
					if(StringUtils.isBlank(val)){
						continue;
					}
					String[] fieldNames = configMap.get(columnName);
						for(String fieldName : fieldNames){
						Class c;
						Method method;
						Object obj;
						try {
							obj = station;
							c = station.getClass().getDeclaredField(fieldName).getType();
							method = station.getClass().getMethod(String.format("set%s%s", Character.toUpperCase(fieldName.charAt(0)), fieldName.substring(1)), c);
						} catch (NoSuchFieldException  e) {
							try {
								obj = station;
								c = station.getClass().getSuperclass().getDeclaredField(fieldName).getType();
								method = station.getClass().getSuperclass().getMethod(String.format("set%s%s", Character.toUpperCase(fieldName.charAt(0)), fieldName.substring(1)), c);
							} catch (NoSuchFieldException  e2) {
								obj = station.getStationRecord();
								c = station.getStationRecord().getClass().getDeclaredField(fieldName).getType();
								method = station.getStationRecord().getClass().getMethod(String.format("set%s%s", Character.toUpperCase(fieldName.charAt(0)), fieldName.substring(1)), c);
							}
						}
						if(c.equals(String.class)){
							method.invoke(obj, val);
						}else if(c.equals(Date.class)){
							try {
								method.invoke(obj, sdf.parse(val));
							} catch (ParseException e) {
								errorMsg.append(String.format("第%d行第%s列日期格式错误（yyyy/MM/dd）！", rowNum-2, columnName));
							}
						}else if(c.equals(BigDecimal.class)){
							try {
								method.invoke(obj, BigDecimal.valueOf(Double.parseDouble(val)));
							} catch (NumberFormatException e) {
								errorMsg.append(String.format("第%d行第%s列格式错误（必须为数字）！", rowNum-2, columnName));
							}
						}else if(c.equals(Project.class)){
							Project project = projectFinder.getProjectByName(val);
							if (project != null && project.getId() != null) {
								method.invoke(obj, project);
							} else {
								errorMsg.append(String.format("第%d行第%s列项目【%s】不存在！", rowNum-2, columnName, val));
							}
						}else if(c.equals(User.class)){
							List<User> users = entityManager.findList("select u from User u where u.deleted = false and u.privileged = false and (u.name like ? or u.account like ?)",
                                    new Object[] { "%" + val + "%", "%" + val + "%" });
							
							if(null != users && !users.isEmpty()){
								if(users.size() == 1){
									method.invoke(obj, users.get(0));
								}else {
									errorMsg.append(String.format("第%d行第%s列人员【%s】存在重复记录！", rowNum-2, columnName, val));
								}
							}else {
								errorMsg.append(String.format("第%d行第%s列人员【%s】不存在！", rowNum-2, columnName, val));
							}
						}else if(c.equals(IndicatorValue.class)){
							IndicatorValue indicatorValue = indicatorFinder.getIndicatorValueByName(val, fieldName);
							if (indicatorValue != null && indicatorValue.getId() != null) {
								method.invoke(obj, indicatorValue);
							} else {
								errorMsg.append(String.format("第%d行第%s列指标值【%s】不存在！", rowNum-2, columnName, val));
							}
						}
					}
				}else{
					errorMsg.append(String.format("第%d行第%s列配置不存在！", rowNum-2, columnName));
				}
			}
			if (station != null) {
				stations.add(station);
			}
		}
		// }
		return stations;
	}
	
    /**
     * 根据col返回excel列名，A-IV
     * @param col
     * @return
     */
    private String getColumnName(int col) {
        int a = col/26;
        int b = col % 26;
        return String.format("%s%s", getChar(a), getChar(b+1));
    }
    private String getChar(int a) {
        switch(a){
            case 1:  return "A";
            case 2:  return "B";
            case 3:  return "C";
            case 4:  return "D";
            case 5:  return "E";
            case 6:  return "F";
            case 7:  return "G";
            case 8:  return "H";
            case 9:  return "I";
            case 10: return "J";
            case 11: return "K";
            case 12: return "L";
            case 13: return "M";
            case 14: return "N";
            case 15: return "O";
            case 16: return "P";
            case 17: return "Q";
            case 18: return "R";
            case 19: return "S";
            case 20: return "T";
            case 21: return "U";
            case 22: return "V";
            case 23: return "W";
            case 24: return "X";
            case 25: return "Y";
            case 26: return "Z";
        }
        return "";
    }
	private String readValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		String value = "";
		int rowType = cell.getCellType();
		if (rowType == Cell.CELL_TYPE_STRING) {
			value = cell.getStringCellValue();
		} else if (rowType == Cell.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
			} else {
				Number v = cell.getNumericCellValue();
				value = v.toString();
			}
		}
		return value;
	}

}
